import xlrd
from sqlalchemy import MetaData, Column, Table, ForeignKey
from sqlalchemy import Integer, String
from sqlalchemy.sql import select
from sqlalchemy.sql import and_
import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import sys
#sys.path.append("../Data")
#sys.path.append("../Model")
from modl_make_bdd import CLS_Table

class CLS_ModlInsertBdd():

   def funcMakeDataBase(self, xlsSheet):

      engine = create_engine('sqlite:///Inscritos_2010-2011-bdd.db', echo = False)
      Session = sessionmaker( bind = engine)
      session = Session()
      # List Storage Values from Line
      listbdd = []
      # BDD 1 COL
      ESTAB_ENSINO = ''
      # BDD 2 COL
      UNI_ORGANICA = ''
      # BDD 3 COL
      NIVEL_FORMACAO = ''
      # BDD 4 COL
      CURSO_SUP = ''
      # BDD 5 COL
      AREA_EDU_FOR = ''

      self.xlsSheet = xlsSheet

      # Bol Check Empty COL Title XLS on Sheet
      checkCol = 0
      # If COL Title empty
      for col in range(self.xlsSheet.ncols):
          if (self.xlsSheet.cell(3,col).value == ""):
              checkCol = col
      # Loop Rows
      for rownum in range(4, self.xlsSheet.nrows, 1):
         # If las - END
         if self.xlsSheet.cell_value(rownum,0) == 'TOTAL':
            break
         else:
            # Fill 5 First ROWS - If Empty fill with Last One Valid
            if self.xlsSheet.cell_value(rownum,0) != '':
               ESTAB_ENSINO = unicode(self.xlsSheet.cell_value(rownum, 0))
            if self.xlsSheet.cell_value(rownum, 1) != '':
               UNI_ORGANICA = unicode(self.xlsSheet.cell_value(rownum, 1))
            if self.xlsSheet.cell_value(rownum,2) != '':
               NIVEL_FORMACAO = unicode(self.xlsSheet.cell_value(rownum, 2))
            if self.xlsSheet.cell_value(rownum,3) != '':
               CURSO_SUP = unicode(self.xlsSheet.cell_value(rownum, 3))
            if self.xlsSheet.cell_value(rownum,4) != '':
               AREA_EDU_FOR = unicode(self.xlsSheet.cell_value(rownum, 4))

            # COLUNAS
            listbdd[:] = []
            # Loop Cols
            for col in range(self.xlsSheet.ncols):
               if (col != checkCol):
                  # If no value - Fill 0.0
                  if self.xlsSheet.cell(rownum,col).value == "-":
                     listbdd.append(unicode("0.0"))
                  elif isinstance(self.xlsSheet.cell(rownum,col).value, float):
                     # If is a Float
                     listbdd.append(unicode((self.xlsSheet.cell(rownum,col).value)))
                  else:
                     # If First 5 Empty - Fill With Last Valid
                     if (self.xlsSheet.cell(rownum,col).value) == '' and col == 0:
                       listbdd.append(ESTAB_ENSINO)
                     elif (self.xlsSheet.cell(rownum,col).value) == '' and col == 1:
                       listbdd.append(UNI_ORGANICA)
                     elif (self.xlsSheet.cell(rownum,col).value) == '' and col == 2:
                       listbdd.append(NIVEL_FORMACAO)
                     elif (self.xlsSheet.cell(rownum,col).value) == '' and col == 3:
                       listbdd.append(CURSO_SUP)
                     elif (self.xlsSheet.cell(rownum,col).value) == '' and col == 4:
                       listbdd.append(AREA_EDU_FOR)
                     else:
                        listbdd.append(unicode(self.xlsSheet.cell(rownum,col).value)) 
            # Object Class BDD Values
            session.add(CLS_Table(
                                 listbdd[0], 
                                 listbdd[1], 
                                 listbdd[2], 
                                 listbdd[3], 
                                 listbdd[4], 
                                 listbdd[5], 
                                 listbdd[6], 
                                 listbdd[7], 
                                 listbdd[8], 
                                 listbdd[9], 
                                 listbdd[10],                            
                                 listbdd[11], 
                                 listbdd[12], 
                                 listbdd[13], 
                                 listbdd[14], 
                                 listbdd[15], 
                                 listbdd[16], 
                                 listbdd[17], 
                                 listbdd[18], 
                                 listbdd[19], 
                                 listbdd[20],                            
                                 listbdd[21], 
                                 listbdd[22], 
                                 listbdd[23], 
                                 listbdd[24], 
                                 listbdd[25], 
                                 listbdd[26], 
                                 listbdd[27], 
                                 listbdd[28], 
                                 listbdd[29],
                                 listbdd[30],                            
                                 listbdd[31], 
                                 listbdd[32], 
                                 listbdd[33], 
                                 listbdd[34], 
                                 listbdd[35], 
                                 listbdd[36], 
                                 listbdd[37], 
                                 listbdd[38], 
                                 listbdd[39], 
                                 listbdd[40],                            
                                 listbdd[41], 
                                 listbdd[42], 
                                 listbdd[43], 
                                 listbdd[44], 
                                 listbdd[45], 
                                 listbdd[46], 
                                 listbdd[47], 
                                 listbdd[48], 
                                 listbdd[49], 
                                 listbdd[50], 
                                 listbdd[51], 
                                 listbdd[52]
                                 ))
               # FIM COLUNAS

         # FIM LINHAS

      # commit the record the database
      if session.commit() != 0:
         return True
      else:
         return False